//201902104014付饶
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.Major;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;
import util.JdbcHelper;

import java.sql.*;
import java.util.Collection;
import java.util.TreeSet;

public final class MajorDao {
    private static MajorDao majorDao = new MajorDao();


    private MajorDao() {
    }



    public static MajorDao getInstance() {
        return majorDao;
    }


    public static Collection<Major> findAll() throws SQLException {
        Connection connection = JdbcHelper.getConn();
        Statement stmt = connection.createStatement();
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = stmt.executeQuery("select * from major");
        Collection<Major> majors = new TreeSet<Major>();
        while (resultSet.next()) {
            majors.add(new Major(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    resultSet.getString("no"),
                    resultSet.getString("remarks"),
                    DepartmentService.getInstance().find(resultSet.getInt("department_id"))
            ));
        }
        JdbcHelper.close(resultSet, stmt, connection);
        return majors;
    }

    public static Major find(int id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String find_sql = "select * from major where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        Major major = new Major(id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"),
                DepartmentService.getInstance().find(resultSet.getInt("department_id"))
        );
        JdbcHelper.close(resultSet, pstmt, connection);
        return major;
    }

    public static boolean update(Major major) throws SQLException {
        //获得连接对象
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String update_sql = "update major set description=?,no=?,remarks=? ,department_id=? where id=?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setString(1, major.getDescription());
        pstmt.setString(2, major.getNo());
        pstmt.setString(3, major.getRemarks());
        pstmt.setInt(4, major.getDepartment().getId());
        pstmt.setInt(5, major.getId());

        //根据执行结果，具体判断所要返回的值
        if (pstmt.executeUpdate() == 0) {
            JdbcHelper.close(pstmt, connection);
            return false;
        } else
            JdbcHelper.close(pstmt, connection);
            return true;
    }

    public static boolean add(Major major) throws SQLException {

        //获得连接对象
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String add_sql =
                "insert into major (description,no,remarks,department_id) values " +
                        "(?,?,?,?);";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(add_sql);
        //为预编译参数赋值

        pstmt.setString(1, major.getDescription());
        pstmt.setString(2, major.getNo());
        pstmt.setString(3, major.getRemarks());
        pstmt.setInt(4, major.getDepartment().getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(pstmt, connection);
        //根据结果，具体判断所要返回的值
        if (affectedRowNum == 1) {
            return true;
        } else
            return false;
    }

    public static boolean delete(int id) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String delete_sql = "DELETE FROM major WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(delete_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);

        if (pstmt.executeLargeUpdate() == 1) {
            //关闭连接
            JdbcHelper.close(pstmt, connection);
            return true;
        } else {
            //关闭连接
            JdbcHelper.close(pstmt, connection);
            return false;
        }
    }

    public boolean delete(Major major) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String delete_sql = "delete from major where no = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(delete_sql);
        //为预编译参数赋值
        pstmt.setString(1, major.getNo());
        //关闭连接
        JdbcHelper.close(pstmt, connection);
        //根据id删除 并判断是否删除成功
        if (pstmt.executeUpdate() == 0) {
            return false;
        } else {
            return true;
        }
    }
}